Questions 1

(RA operators in SQL). Transform the following relational algebra expressions from the first exercise sheet into equivalent SQL queries.

Question 2



In [1]:

    
import sqlite3
conn = sqlite3.connect('movie.db')
cur = conn.cursor()

a) What is the genre of “Titanic”?



In [2]:

    
cur.execute('''SELECT genre 
               FROM film 
               WHERE title="Titanic"''')
for row in cur.fetchall():
    print(row[0])









    



Drama/Romance/Thriller

b) What is the ID of Angelina Jolie?



In [3]:

    
cur.execute('''SELECT id, firstname, name
               FROM person 
               WHERE name='Jolie'
               AND firstname='Angelina' ''')
for row in cur.fetchall():
    print(row[0], row[1], row[2])









    



1556 Angelina Jolie

c) Which action movies were produced in 2010?



In [4]:

    
cur.execute('''SELECT title 
               FROM film 
               WHERE year=2010 
               AND genre LIKE "%Action%"''')
for row in cur.fetchall():
    print(row[0])









    



Inception
Despicable Me
Iron Man 2
Kick-Ass
The Book of Eli
Megamind
Scott Pilgrim vs. the World
狄仁傑之通天帝國（臺：通天神探狄仁傑
Unthinkable
Undisputed III: Redemption
Super
Batman: Under the Red Hood
Jackass 3D
Rare Exports
Justice League: Crisis on Two Earths
Inception: The Cobol Job
十三人の刺客
Tropa de Elite 2
아저씨

d) List all cinemas located in Bonn.



In [5]:

    
cur.execute('''SELECT name 
               FROM cinema 
               WHERE city="Bonn"''')
for row in cur.fetchall():
    print(row[0])









    



Bonner Kinemathek
Stern Lichtspiele
WOKI - Dein Kino!
Neue Filmbühne
Rex-Lichtspieltheater

e) List all actors who acted in at least one movie.



In [6]:

    
cur.execute('''SELECT  firstname, name 
               FROM person, participation 
               WHERE participation.person = person.id
               AND function="actor"''')
for row in cur.fetchall()[:10]:
    print(row[0], row[1])









    



Felicity Jones
Diego Luna
Alan Tudyk
Donnie Yen
Wen Jiang
Ben Mendelsohn
Forest Whitaker
Riz Ahmed
Mads Mikkelsen
Jimmy Smits

f) Who directed the film “Titanic”?



In [7]:

    
cur.execute('''SELECT  firstname, name
               FROM person, participation, film
               WHERE participation.person = person.id 
               AND participation.function="director"
               AND participation.film = film.id
               AND film.title="Titanic"''')
for row in cur.fetchall():
    print(row[0], row[1])









    



James Cameron

g) Where can you watch the new Dan Brown film “Inferno”?

As the dates in the SHOW table are randomly chosen between 1980 and 2016, every cinema shows ever film only once in this time and there are only cinemas from Bonn and Cologne in the cinema table there are not to many matches.



In [8]:

    
cur.execute('''SELECT  cinema.name, cinema.city
               FROM cinema, show, film
               WHERE show.date > '1999-' 
               AND film.title="Inferno"
               AND show.film = film.id
               AND show.cinema = cinema.id''')
for row in cur.fetchall():
    print(row[0], row[1])

h) Who participated in “Inferno”?



In [9]:

    
cur.execute('''SELECT  person.firstname, person.name
               FROM person, participation, film
               WHERE film.title = "Inferno"
               AND participation.film = film.id
               AND participation.person = person.id''')
for row in cur.fetchall():
    print(row[0], row[1])



In [10]:

    
conn.close()